package dao.student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.UUID;
import Test.StringUtil;
import Test.TestDB;

public class Dao extends TestDB{
//  ssss
    public HashMap SearchStudents(String key, int index, int size, String sortField, String sortOrder) throws Exception
    {
        //System.Threading.Thread.Sleep(300);
    if(key == null) key = "";
    String sql =
    "select a.*\n"
    
           +"from t_student a \n"
           
           +"where a.name like '%" + key + "%' \n";
        if (StringUtil.isNullOrEmpty(sortField) == false)
        {
            if ("desc".equals(sortOrder) == false) sortOrder = "asc";
            sql += " order by " + sortField + " " + sortOrder;
        }
        else
        {
            sql += " order by createtime desc";
        }
        ArrayList dataAll = DBSelect(sql);
        ArrayList data = new ArrayList();
        int start = index * size, end = start + size;
        for (int i = 0, l = dataAll.size(); i < l; i++)
        {
            HashMap record = (HashMap)dataAll.get(i);
            if (record == null) continue;
            if (start <= i && i < end)
            {
                data.add(record);
            }
            record.put("createtime", new Timestamp(100,10,10,1,1,1,1));
        }
        HashMap result = new HashMap();
        result.put("data", data);
        result.put("total", dataAll.size());
        //minAge, maxAge, avgAge
        ArrayList ages = DBSelect("select min(age) as minAge, max(age) as maxAge, avg(age) as avgAge from t_student");
        HashMap ageInfo = (HashMap)ages.get(0);
        result.put("minAge", ageInfo.get("minAge"));
        result.put("maxAge", ageInfo.get("maxAge"));
        result.put("avgAge", ageInfo.get("avgAge"));
        return result;
    }

    public HashMap GetStudent(String id) throws Exception
    {
    	String sql = "select * from t_student where id = '"+id+"'";
        ArrayList data = DBSelect(sql);
        return data.size() > 0 ? (HashMap)data.get(0) : null;
    }

    public String InsertStudent(HashMap user) throws Exception
    {
    	String id = (user.get("id") == null || user.get("id").toString().equals(""))? UUID.randomUUID().toString() : user.get("id").toString();
        user.put("id", id);
        if (user.get("name") == null) user.put("name", "");
        if (StringUtil.isNullOrEmpty(user.get("gender"))) user.put("gender", 0);
    	Connection conn = TestDB.getConn();
    	String sql = "insert into t_student (id,name,age,birthday,gender,country,city,remarks,school,createtime,email)"
       	 + " values(?,?,?,?,?,?,?,?,?,?,?)";
       PreparedStatement stmt = conn.prepareStatement(sql);
       
                  stmt.setString(1,ToString(user.get("id")));
              
                  stmt.setString(2,ToString(user.get("name")));
              
                  stmt.setInt(3,ToInt(user.get("age")));
              
                  stmt.setDate(4,ToDate(user.get("birthday")));
              
                  stmt.setInt(5,ToInt(user.get("gender")));
              
                  stmt.setString(6,ToString(user.get("country")));
              
                  stmt.setString(7,ToString(user.get("city")));
              
                  stmt.setString(8,ToString(user.get("remarks")));
              
                  stmt.setString(9,ToString(user.get("school")));
              
                  stmt.setDate(10,ToDate(user.get("createtime")));
              
                  stmt.setString(11,ToString(user.get("email")));
              
       stmt.executeUpdate();
        stmt.close();
       conn.close();
        return id;
    }

    public void DeleteStudent(String userId) throws Exception
    {
       Connection conn = getConn();
       Statement stmt = conn.createStatement();
        String sql = "delete from t_student where id = \""+userId+"\"";
        stmt.executeUpdate(sql);
       stmt.close();
       conn.close();
    }

    public void UpdateStudent(HashMap user) throws Exception
    {
        HashMap db_user = GetStudent(user.get("id").toString());
        Iterator iter = user.entrySet().iterator();
        while (iter.hasNext()) {
            Map.Entry entry = (Map.Entry) iter.next();
            Object key = entry.getKey();
            Object val = entry.getValue();
            db_user.put(key, val);
        }
        DeleteStudent(user.get("id").toString());
        InsertStudent(db_user);
    }


}

 